package com.java.business.system.common.service;

import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.java.business.report.bean.T_REPORT_TPL;
import com.java.business.report.dao.ReportMapper;
import com.java.business.system.common.bean.TreeParam;
import com.java.business.system.common.dao.CommonMapper;
import com.java.core.bean.LowerCaseKeyMap;
import com.java.core.excel.ExcelModel;
import com.java.core.excel.ExcelTemplatelHandler;
import com.java.core.utils.ReplaceUtils;
import com.java.core.utils.RequestUtils;

@Service
public class CommonService {
	@Autowired
	private CommonMapper mapper;

	@Autowired
	private ReportMapper reportMapper;

	@Autowired
	private JdbcTemplate jdbc;

	public LowerCaseKeyMap queryForMap(Map<String, String> params) {
		return mapper.queryForMap(params);
	}

	public List<LowerCaseKeyMap> queryForList(Map<String, String> params) {
		return mapper.queryForList(params);
	}

	public List<LowerCaseKeyMap> queryBySql(Map<String, String> params) {
		List<LowerCaseKeyMap> list = mapper.queryBySql(params);
		// 合计
		total(params, list);
		return list;
	}

	public Integer queryCount(Map<String, String> params) {
		String countSql = params.get("countSql");
		if (StringUtils.isBlank(countSql)) {
			return 0;
		}
		return jdbc.queryForObject(countSql, Integer.class);
	}

	/**
	 * 求合计
	 * 
	 * @param params
	 * @param list
	 */
	public void total(Map<String, String> params, List<LowerCaseKeyMap> list) {
		String totalSql = params.get("totalSql");
		String datacolsstr = params.get("datacols");
		if (StringUtils.isBlank(totalSql) || StringUtils.isBlank(datacolsstr)) {
			return;
		}
		String[] datacols = datacolsstr.split(",");
		params = new HashMap<String, String>();
		params.put("sql", totalSql);
		List<LowerCaseKeyMap> totalList = mapper.queryBySql(params);
		LowerCaseKeyMap totalRecord = new LowerCaseKeyMap();
		for (String datacol : datacols) {
			double total = 0;
			for (LowerCaseKeyMap data : totalList) {
				total += Double.valueOf(String.valueOf(data.get(datacol)));
			}
			totalRecord.put(datacol, total);
		}
		list.add(0, totalRecord);
	}

	public void exec(Map<String, String> params) throws RuntimeException {
		params.put("sql", ReplaceUtils.replace(params.get("sql")));
		mapper.exec(params);
	}

	public List<LowerCaseKeyMap> queryForTree(TreeParam params) {
		String sql = "select * from (select t.*,t." + params.getValueField()
				+ "||t." + params.getDisplayField() + " display_name from "
				+ params.getTable() + " t order by " + params.getValueField()
				+ ") where ";
		boolean hasLeaf = StringUtils.isNotBlank(params.getLeafField());
		boolean hasQuery = StringUtils.isNotBlank(params.getQuery());
		if (hasQuery) {
			if ("leaf".equals(params.getSelectMode())) {
				sql += params.getLeafField() + "='1' and display_name like '%"
						+ params.getQuery() + "%' ";
			} else {
				sql += "display_name like '%" + params.getQuery() + "%' ";
			}
		} else {
			if ("all".equals(params.getRootValue())) {
				sql += params.getParentField() + " is null ";
			} else {
				sql += params.getParentField() + "='" + params.getRootValue()
						+ "' ";
			}
		}
		sql += params.getQueryWhere();
		Map<String, String> sqlParams = new HashMap<String, String>();
		sqlParams.put("sql", sql);
		List<LowerCaseKeyMap> list = mapper.queryBySql(sqlParams);
		if (hasQuery) {
			for (LowerCaseKeyMap map : list) {
				if (hasLeaf) {
					map.put(params.getLeafField(), 1);
				} else {
					map.put("leaf", true);
				}
			}
		}
		return list;
	}

	public String queryDefault(TreeParam params) {
		String display = params.getDisplayField();
		if (params.getJoinCode() != null && params.getJoinCode()) {
			display = params.getValueField() + "||" + display;
		}
		String sql = "select wm_concat(" + display + ") display from "
				+ params.getTable() + " where " + params.getValueField()
				+ " in('" + params.getValue().replaceAll(",", "','") + "')";
		return jdbc.queryForObject(sql, String.class);
	}

	/**
	 * 导出excel
	 * 
	 * @param params
	 */
	public void export2Excel(HttpServletResponse response,
			HttpServletRequest request) throws IOException,
			InvalidFormatException {
		Map<String, String> params = RequestUtils.getParams(request);
		List<LowerCaseKeyMap> list = queryBySql(params);
		String exceltpl = params.get("exceltpl");
		Integer excelstart = Integer.valueOf(params.get("excelstart"));
		String name = (String) params.get("name");
		String filename = name + ".xls";
		ExcelModel em = ExcelModel.XLS;
		if (list.size() > 10000) {
			em = ExcelModel.XLSX_BIGDATA;
			filename = name + ".xlsx";
		}
		// 设置header
		response.reset();
		response.setHeader("content-disposition", "attachment;filename=\""
				+ URLEncoder.encode(filename, "UTF-8") + "\"");
		response.setHeader("connection", "close");
		// 输出流
		OutputStream out = new BufferedOutputStream(response.getOutputStream());
		// 模板
		InputStream in;
		T_REPORT_TPL tpl = reportMapper.getTpl(exceltpl);
		byte[] binary = tpl.getTpl();
		if (binary != null) {
			in = new ByteArrayInputStream(binary);
			// 执行导出
			ExcelTemplatelHandler handler = new ExcelTemplatelHandler(em);
			handler.export(out, in, excelstart, new LowerCaseKeyMap(), list);
		}
		throw new RuntimeException("对不起，没有找到Excel模板。");
	}
}
